﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Drawing;

namespace newTurtle8
{
    class Company
    {
        /*string battalionName;
        string name;
        string corpsType;*/

        static private Company instance = new Company();
        public event SoldierAddedEventHandler SoldierAdded;
        public event SoldierRemovedEventHandler SoldierRemoved;
        public event SoldierUpdatedEventHandler SoldierUpdated;


        private Company()
        {
            //To-Do
            //assign battalion Name etc...
        }

        static public Company getInstance() { return instance;}

        public void addSoldier(Soldier soldier)
        {
            SqlManager.CommandType cmdType;

            if (!Soldier.isExists(soldier.ArmyID))
                cmdType = SqlManager.CommandType.Insert;
            else cmdType = SqlManager.CommandType.Update;
            
            //there isn't a soldier with this armyID in database --->>> add this new soldier to 4 databases:
           
            try
            {
                //add data to the different tables
                addPersonalDataToDB(cmdType, soldier);
                addFamilyInfoToDB(cmdType, soldier);
                addContactInfoToDB(cmdType, soldier);
                addArmyInfoToDB(cmdType, soldier);

                SoldierAdded(this, new SoldierDataEventArgs(soldier.ArmyID));
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }
        public void removeSoldier(string armyID)
        {         
            //retrieve the meta data of the tabels in this connection DataBase.
            SqlCommand tablesSqlCmd = SqlManager.SqlCommandFactory( @"SELECT * FROM INFORMATION_SCHEMA.TABLES");            
            DataTable dt = SqlManager.selectSqlQueryDataTable(tablesSqlCmd);

            //deleting the associated 'armyID' from all tables in database
            foreach (DataRow row in dt.Rows)
            {
                // set the table-to-delete data
                SqlCommand deleteSqlCmd = SqlManager.SqlCommandFactory(@"DELETE FROM table_name WHERE armyID = @armyID");
                deleteSqlCmd.CommandText = deleteSqlCmd.CommandText.Replace("table_name", (string)row["TABLE_NAME"]);
                deleteSqlCmd.Parameters.Add(new SqlParameter("@armyID", armyID));

                SqlManager.executeNonQuery(deleteSqlCmd);
            }

            SoldierRemoved(this, new SoldierDataEventArgs(armyID));
        }
        public Soldier getSoldier(string armyID)
        {
            return new Soldier(armyID);
        }

        private void addPersonalDataToDB(Enum cmdType, Soldier s)
        {
            SqlCommand sqlCmd = SqlManager.SqlCommandFactory(SqlManager.getSqlStringFromFile(@"\" + cmdType.ToString() + @"\PersonalData." + cmdType.ToString() + ".sql"));
            sqlCmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = s.FirstName;
            sqlCmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = s.LastName;
            sqlCmd.Parameters.Add("@socialID", SqlDbType.NVarChar).Value = s.SocialID;
            sqlCmd.Parameters.Add("@armyID", SqlDbType.NVarChar).Value = s.ArmyID;
            sqlCmd.Parameters.Add("@homePhone", SqlDbType.NVarChar).Value = s.HomePhone;
            sqlCmd.Parameters.Add("@cellPhone", SqlDbType.NVarChar).Value = s.CellPhone;
            sqlCmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = s.Email;
            sqlCmd.Parameters.Add("@birthday", SqlDbType.Date).Value = s.Birthday;
            sqlCmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = s.City;
            sqlCmd.Parameters.Add("@street", SqlDbType.NVarChar).Value = s.Street;
            sqlCmd.Parameters.Add("@buildingNum", SqlDbType.SmallInt).Value = s.BuildingNum;
            sqlCmd.Parameters.Add("@apartmentNum", SqlDbType.SmallInt).Value = s.ApartmentNum;
            if (s.Picture == null)
                sqlCmd.Parameters.Add("@picture", SqlDbType.VarBinary).Value = DBNull.Value;
            else sqlCmd.Parameters.Add("@picture", SqlDbType.VarBinary).Value = CompanyManager.imageToByteArray(s.Picture);
            
            sqlCmd.Parameters.Add("@comments", SqlDbType.NVarChar).Value = s.comments;

            SqlManager.executeNonQuery(sqlCmd);
        }
        private void addFamilyInfoToDB(Enum cmdType, Soldier soldier)
        {
            SqlCommand sqlCmd = SqlManager.SqlCommandFactory(SqlManager.getSqlStringFromFile(@"\" + cmdType.ToString() + @"\FamilyInfo." + cmdType.ToString() + ".sql"));
            sqlCmd.Parameters.Add("@armyID", SqlDbType.NVarChar).Value = soldier.ArmyID;
            sqlCmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = EnumUtils.getHebDescription(soldier.CoupleStatus);
            sqlCmd.Parameters.Add("@wifeName", SqlDbType.NVarChar).Value = soldier.WifeName;
            sqlCmd.Parameters.Add("@childrenCtr", SqlDbType.TinyInt).Value = soldier.ChildrenCtr;
            sqlCmd.Parameters.Add("@childrenNames", SqlDbType.NVarChar).Value = soldier.ChildrenNames;

            SqlManager.executeNonQuery(sqlCmd);
        }
        private void addContactInfoToDB(Enum cmdType, Soldier soldier)
        {
            SqlCommand sqlCmd = SqlManager.SqlCommandFactory(SqlManager.getSqlStringFromFile(@"\" + cmdType.ToString() + @"\ContactInfo." + cmdType.ToString() + ".sql"));
            sqlCmd.Parameters.Add("@armyID", SqlDbType.NVarChar).Value = soldier.ArmyID;
            sqlCmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = soldier.ContactFirstName;
            sqlCmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = soldier.ContactLastName;
            sqlCmd.Parameters.Add("@homePhone", SqlDbType.NVarChar).Value = soldier.ContactHomePhone;
            sqlCmd.Parameters.Add("@cellPhone", SqlDbType.NVarChar).Value = soldier.contactCellPhone;
            sqlCmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = soldier.ContactEmail;
            sqlCmd.Parameters.Add("@relation", SqlDbType.NVarChar).Value = EnumUtils.getHebDescription(soldier.ContactRelation);
            sqlCmd.Parameters.Add("@adress", SqlDbType.NVarChar).Value = soldier.ContactAdress;

            SqlManager.executeNonQuery(sqlCmd);
        }
        private void addArmyInfoToDB(Enum cmdType, Soldier s)
        {
            SqlCommand sqlCmd = SqlManager.SqlCommandFactory(SqlManager.getSqlStringFromFile(@"\" + cmdType.ToString() + @"\ArmyInfo." + cmdType.ToString() + ".sql"));
            sqlCmd.Parameters.Add("@armyID", SqlDbType.NVarChar).Value = s.ArmyID;
            sqlCmd.Parameters.Add("@position", SqlDbType.NVarChar).Value = s.Position;
            sqlCmd.Parameters.Add("@arrivalDate", SqlDbType.Date).Value = s.ArrivalDate;
            sqlCmd.Parameters.Add("@platoon", SqlDbType.TinyInt).Value = s.Platoon;
            sqlCmd.Parameters.Add("@shoeSize", SqlDbType.TinyInt).Value = s.ShoeSize;
            sqlCmd.Parameters.Add("@shoeStatus", SqlDbType.NVarChar).Value = EnumUtils.getHebDescription(s.ShoeStatus);
            sqlCmd.Parameters.Add("@idDiscStatus", SqlDbType.NVarChar).Value = EnumUtils.getHebDescription(s.IdDiscStatus);
            sqlCmd.Parameters.Add("@isActive", SqlDbType.Bit).Value = s.IsActive;

            SqlManager.executeNonQuery(sqlCmd);
            addQualificationsToDB(s);
            addToFitnessTrack(s.ArmyID);
        }

        private void addQualificationsToDB(Soldier s)
        {
            //deletes all qualifications for this soldier
            SqlCommand deleteCmd = SqlManager.SqlCommandFactory("DELETE FROM Qualifications WHERE armyID=@armyID");
            deleteCmd.Parameters.Add("@armyID", SqlDbType.NVarChar).Value = s.ArmyID;
            SqlManager.executeNonQuery(deleteCmd);

            //inserts all qualifications to DB.
            SqlCommand insertCmd = SqlManager.SqlCommandFactory("INSERT into Qualifications (armyID,qualification) values (@armyID,@qual)");
            insertCmd.Parameters.Add("@armyID",SqlDbType.NVarChar).Value = s.ArmyID;
            insertCmd.Parameters.Add("@qual",SqlDbType.NVarChar);

            foreach (string qual in s.QualifiedList)
            {
                insertCmd.Parameters["@qual"].Value = qual;
                SqlManager.executeNonQuery(insertCmd);
            }

        }
        //adds an entry into [FitnessTrack] DB. 1 entry per each armyID.
        private void addToFitnessTrack(string armyID)
        {
            SqlCommand insertCmd = SqlManager.SqlCommandFactory("INSERT INTO FitnessTrack (armyID) Values (@armyID)");
            insertCmd.Parameters.Add("@armyID", SqlDbType.NVarChar).Value = armyID;
            SqlManager.executeNonQuery(insertCmd);
        }
    }
}
